#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
Created on 2015-3-28
@author: zhaoxiang
'''
from zp_user_report import DBConfig
import HandleMysql
import format_time
import sys
import os
import postUtils
import load_data

def getSuccessData(searchDate):
    month=format_time.get_month2(searchDate)
    sql=("select stat_date,unit_id,'0',case platform when -1 then 'PC' when 0 then 'M' when 1 then 'WAP'  when 2 then 'APP' else platform end, "
         " ,disp_city1,disp_cate1_id,disp_cate1,disp_cate2_id,disp_cate2, "
         " sum(click_amount),sum(cash_amount),sum(gift_amount),"
         " sum(pv),sum(click),sum(click)/sum(pv),sum(click_amount)/sum(click), "
         " bid,budget,sum(click_amount)/budget,if(sum(click_amount)/budget>1,1,0),"
         " user_id,is_vip,'0' "
         " from d_jingzhun_cpc_all_stat_%s"
         " where stat_date='%s'"
         " and unit_id > 0 "
         " group by user_id,unit_id,disp_city1,disp_cate1,disp_cate2" % (month,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_27_jingzhun)
        handleMysql.connect()
        rowSet= handleMysql.get_result(sql)
    finally:
        handleMysql.close()
    return rowSet

def getSuccessUIDList(searchDate):
    month=format_time.get_month2(searchDate)
    sql=("select distinct user_id"
         " from d_jingzhun_cpc_all_stat_%s"
         " where stat_date='%s'"
         " and unit_id > 0 "  % (month,searchDate))
    try:
        handleMysql = HandleMysql(DBConfig.db_27_jingzhun)
        handleMysql.connect()
        rowSet= handleMysql.get_result(sql)
    finally:
        handleMysql.close()
    return rowSet

def getAccountInfo(searchDate,uidList):
    month=format_time.get_month2(searchDate)
    idInCon = "";
    try:
        for uid in uidList:
            idInCon += str(uid)+","
        idInCon = idInCon[0:-1]
        sql=("select user_id"
             " sum( if(type in(2,3),free_money,0))  AS '总账户', "
             " sum( if(type=2,free_money,0))  as '现金账户', "
             " sum( if(type=3,free_money,0))  AS '优惠账户' "         
             " from d_common_user_account_balance_%s"
             " where stat_date='%s'"
             " and user_id in(%s)"
            " group by user_id "  % (month,searchDate,idInCon))
        handleMysql = HandleMysql(DBConfig.db_27_detail)
        handleMysql.connect()
        rowDic= handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return rowDic
 
def getDispCateInfo():
    try:
        sql=("select cate_id,cate_name,cate1_id,cate1_name,cate2_id,cate2_name,cate3_id,cate3_name,"
             " business_type,business_type_name,business_type_biz,business_type_name_biz"         
             " from m_common_disp_cate"
             )
        handleMysql = HandleMysql(DBConfig.db_27_middle)
        handleMysql.connect()
        rowDic= handleMysql.get_as_dict(sql)
    finally:
        handleMysql.close()
    return rowDic

def matchBusinessType(dispCateInfo,cate1_id,cate2_id):
    dispInfo = dispCateInfo.get(cate2_id)
    if dispInfo:
        return dispInfo[10]
    dispInfo = dispCateInfo.get(cate1_id)
    if dispInfo:
        return dispInfo[10]
    return ''
creat_table_sql='''
CREATE TABLE `t_pre_cpc_success_detail_stat_%s` (
  `stat_date` varchar(255) DEFAULT NULL COMMENT '日期',
  `creative_id` bigint(20) DEFAULT NULL COMMENT '推广ID',
  `page_type` int(11) DEFAULT NULL COMMENT '页面类型',
  `platform` varchar(255) DEFAULT NULL COMMENT '平台 PC:-1 M:0 WAP:1 APP:2',
  `disp_city1` varchar(255) DEFAULT NULL COMMENT '一级城市',
  `business_type` varchar(255) NOT NULL DEFAULT '' COMMENT '业务线(拼音)',
  `disp_cate1_id` int(11) DEFAULT NULL COMMENT '展现一级类别id',
  `disp_cate1` varchar(255) DEFAULT NULL COMMENT '一级类别',
  `disp_cate2_id` int(11) DEFAULT NULL COMMENT '展现二级类别id',
  `disp_cate2` varchar(255) DEFAULT NULL COMMENT '二级类别',
  `amount` double DEFAULT NULL COMMENT '消费（分）',
  `cash` double DEFAULT NULL COMMENT '现金（分）',
  `gift` double DEFAULT NULL COMMENT '优惠（分）',
  `pv` bigint(20) DEFAULT NULL COMMENT '展示PV',
  `click` bigint(20) DEFAULT NULL COMMENT '点击',
  `ctr` double DEFAULT NULL COMMENT 'CTR',
  `actual_click_price` double DEFAULT NULL COMMENT '实际点击价格（分）',
  `bid` double DEFAULT NULL COMMENT '设定点击价格（分）',
  `budget` double DEFAULT NULL COMMENT '预算消费（分）',
  `budget_cost_rate` double DEFAULT NULL COMMENT '预算消耗率',
  `is_line` int(11) DEFAULT NULL COMMENT '是否撞线',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户编号',
  `is_vip` int(11) DEFAULT NULL COMMENT '是否会员',
  `is_top` int(11) DEFAULT NULL COMMENT '三个月内是否置顶',
  `account_balance` double DEFAULT NULL COMMENT '总账户余额',
  `cash_account_balance` double DEFAULT NULL COMMENT '现金账户余额',
  `gift_account_balance` double DEFAULT NULL COMMENT '优惠账户余额'
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;
'''
    
def main(searchDate):
    month=format_time.get_month2(searchDate)
    fileName='./results/cpc_success_stat_%s.txt' % searchDate
    successUIDList = getSuccessUIDList(searchDate)
    userAccountDict = getAccountInfo(searchDate,successUIDList)
    allSuccessData = getSuccessData(searchDate)
    dispCateInfo = getDispCateInfo()
    with open(fileName) as f:
        for row in allSuccessData:
            userID = row[20]
            assountInfo = userAccountDict.get(userID,[0,0,0])
            
            cate1_id=row[4]
            cate2_id=row[6]
            businessName = matchBusinessType(dispCateInfo,cate1_id,cate2_id)
            
            row.insert(businessName);
#            total = assountInfo[0]
#            cash = assountInfo[1]
#            coupon = assountInfo[2]
            line =",".join(row.extend(assountInfo))
            f.write(line+'\n')
    sql = creat_table_sql % month
    try:
        handleMysql = HandleMysql(DBConfig.db_27_jingzhun)
        handleMysql.connect()
        print('check table!')
        handleMysql.execute_sql(sql)
        load_data.load_data(handleMysql, os.path.abspath(fileName), 't_pre_cpc_success_detail_stat_%s' % month, ',')
    finally:
        handleMysql.close()
    return True

if __name__ == '__main__':
    if len(sys.argv) == 1:
        startDate=format_time.get_yesterday()
        endDate=format_time.get_yesterday()
    elif len(sys.argv) == 2:
        startDate=endDate=sys.argv[1]
    else:
        startDate=sys.argv[1]
        endDate=sys.argv[2]
    try:
        startDate=format_time.format_date(startDate, '-')
        endDate=format_time.format_date(endDate, '-')
        while startDate <= endDate:
            print('%s start deal %s' % (format_time.get_now(),startDate))
            main(startDate)
            print('%s finished %s' % (format_time.get_now(),startDate))
            startDate=format_time.add_date(startDate)
    except Exception as e:
        sys.stderr.write('%s\n' % startDate +str(e))
        postUtils.send_Msg('CPC成功明细python脚本数据%s报错!' % startDate)
    sys.exit(0)   

    
    